/*
 * Copyright 2008-2010 Daniel Cachapa <cachapa@gmail.com>
 * 
 * This program is distributed under the terms of the GNU General Public License Version 3
 * The license can be read in its entirety in the LICENSE.txt file accompanying this source code,
 * or at: http://www.gnu.org/copyleft/gpl.html
 * 
 * This file is part of Libra.
 *
 * WeightWatch is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, version 3 of the License.
 *
 * WeightWatch is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with the WeightWatch source code. If not, see: http://www.gnu.org/licenses
 */

package net.cachapa.libra.data;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.util.Vector;

import net.cachapa.libra.Preferences;
import net.cachapa.libra.util.MyDate;
import net.cachapa.libra.util.Util;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.preference.PreferenceManager;

public class Database extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "WeightWatch";
	private static final int DATABASE_VERSION = 1;
	private static final String TABLE_NAME = "'Values'";
	public static final String ORDER_DESC = "DESC";
	public static final String ORDER_ASC = "ASC";
	public static final String DATABASE_CHANGED = "net.cachapa.libra.DATABASE_CHANGED";
	
	private static Database instance = null;
	
	private Context context;
	private Value goalValue = null;
	private float cachedGoalWeight = 0;
	
	protected Database(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
		regenerateGoalValue();
	}
	
	public static Database getInstance(Context context) {
		if (instance == null) {
			instance = new Database(context);
		}
		return instance;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + TABLE_NAME + " ("
				+ "_id INTEGER PRIMARY KEY, weight REAL, trend REAL, sync BOOLEAN"
				+ ");");
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// FOR GREAT FUTURE!!!
		db.execSQL("DROP TABLE " + TABLE_NAME);
		onCreate(db);
	}
	
	/**
	 * Inserts a value in the database
	 * @param value The value to be inserted
	 * @return true if the value was successfully inserted. False if the value already exists in the database.
	 */
	public boolean insertValue(Value value) {
		Value prevValue = getValueBefore(value.getDate());
		value.setTrend(Util.calculateTrend(prevValue, value));
		
		ContentValues cv = new ContentValues();
		cv.put("_id", value.getId());
		cv.put("weight", value.getWeight());
		cv.put("trend", value.getTrend());
		cv.put("sync", false);
		
		SQLiteDatabase db = getWritableDatabase();
		boolean inserted = false;
		try {
			inserted = db.insert(TABLE_NAME, "0", cv) != -1;
		} catch (SQLiteConstraintException e) {
			// The value already exists on the database
			// We ignore it
		}
		
		// Regenerates the trends for all of the following values
		if (inserted) {
			regenerateTrendValues(value);
		}
		
		sendUpdateIntent();
		return inserted;
	}
	
	public boolean updateValue(Value value) {
		Value prevValue = getValueBefore(value.getDate());
		value.setTrend(Util.calculateTrend(prevValue, value));
		
		ContentValues cv = new ContentValues();
		cv.put("weight", value.getWeight());
		cv.put("trend", value.getTrend());
		
		SQLiteDatabase db = getWritableDatabase();
		boolean updated = db.update(TABLE_NAME, cv, "_id = " + value.getId(), null) == 1;
		
		// Regenerates the trends for all of the following values
		if (updated) {
			regenerateTrendValues(value);
		}
		
		sendUpdateIntent();
		return updated;
	}
	
	public int insertManyValues(Vector<Value> values) {
		if (values == null) {
			return 0;
		}
		
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv;
		int inserted = 0;
		for (Value value : values) {
			cv = new ContentValues();
			cv.put("_id", value.getDate().getDate());
			cv.put("weight", value.getWeight());
			cv.put("trend", 0);
			cv.put("sync", true);	// We set imported values to true by default because of problems with the DailyBurn API
			
			if (db.insert(TABLE_NAME, "0", cv) != -1) {
				inserted++;
			}
		}
		
		// Regenerates the trends for all of the values and the ones following them
		if (inserted > 0) {
			Value earliestValue;
			if (values.firstElement().getDate().isBefore(values.lastElement().getDate())) {
				earliestValue = values.firstElement();
			}
			else {
				earliestValue = values.lastElement();
			}
			regenerateTrendValues(getValueBefore(earliestValue.getDate()));
		}
		
		sendUpdateIntent();
		return inserted;
	}
	
	/* Getters */
	public Vector<Value> getAllValues(String order) {
		Vector<Value> values = new Vector<Value>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, null, null, null,
				null, "_id " + order);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			values.add(new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2)));
			cursor.moveToNext();
		}
		cursor.close();
		return values;
	}
	
	public Vector<Value> getPeriod(MyDate startDate, MyDate endDate, String order) {
		Vector<Value> values = new Vector<Value>();
		String selection = "_id >= " + startDate.getDate() +
				" AND _id <= " + endDate.getDate();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, selection, null, null,
				null, "_id " + order);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			values.add(new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2)));
			cursor.moveToNext();
		}
		cursor.close();
		return values;
	}
	
	/**
	 * Gets the entries for the week starting in date
	 * @param date The first day of the week
	 * @param order ORDER_ASC for ascending, or ORDER_DESC for descending
	 * @return The entries for date + 7 days
	 */
	public Vector<Value> getWeek(MyDate date, String order) {
		MyDate startDate = new MyDate(date);
		MyDate endDate = new MyDate(date);
		endDate.addDays(7);
		return getPeriod(startDate, endDate, order);
	}
	
	public Vector<Value> getMonth(MyDate date, String order) {
		MyDate startDate = new MyDate(date.getYear(), date.getMonth(), 1);
		MyDate endDate = new MyDate(date.getYear(), date.getMonth(), date.getDaysInMonth());
		return getPeriod(startDate, endDate, order);
	}
	
	public Vector<Value> getYear(MyDate date, String order) {
		MyDate startDate = new MyDate(date.getYear(), 1, 1);
		MyDate endDate = new MyDate(date.getYear(), 12, 31);
		return getPeriod(startDate, endDate, order);
	}

	public Value getValueBefore(MyDate date) {
		Value value = null;
		String selection = "_id < " + date.getDate();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, selection, null, null, null, "_id DESC", "1");
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			value = new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2));
		}
		cursor.close();
		return value;
	}
	
	public Value getValueAfter(MyDate date) {
		Value value = null;
		String selection = "_id > " + date.getDate();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, selection, null, null, null, "_id ASC", "1");
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			value = new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2));
		}
		cursor.close();
		return value;
	}
	
	public Vector<Value> getUnsynchedValues() {
		Vector<Value> values = new Vector<Value>();
		String selection = "sync = 0";
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, selection, null, null, null, "_id ASC");
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			values.add(new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2)));
			cursor.moveToNext();
		}
		cursor.close();
		return values;
	}
	
	public int getSizeUnsynchedValues() {
		SQLiteDatabase db = getReadableDatabase();
		SQLiteStatement statement = db.compileStatement("SELECT COUNT(*) FROM " + TABLE_NAME + " WHERE sync = 0");
		int result = (int) statement.simpleQueryForLong();
		statement.close();
		return result;
	}
	
	public void deleteDatabase() {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DROP TABLE " + TABLE_NAME);
		onCreate(db);
		goalValue = null;
		sendUpdateIntent();
	}
	
	public void deleteValue(Value value) {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE _id =" + value.getId());
		
		// Now we need to regenerate the trends for the next values
		regenerateTrendValues(getValueBefore(value.getDate()));
		sendUpdateIntent();
	}

	public Value getFirstValue() {
		Value value = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, "_id ASC", "1");
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			value = new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2));
		}
		cursor.close();
		return value;
	}
	
	public Value getLatestValue() {
		Value value = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, "_id DESC", "1");
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			value = new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2));
		}
		cursor.close();
		return value;
	}
	
	public Value getValueByDate(MyDate date) {
		Value value = null;
		String selection = "_id = " + date.getDate();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, selection, null, null, null, null, "1");
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			value = new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2));
		}
		cursor.close();
		return value;
	}

	public Vector<Value> getValuesAfter(MyDate date) {
		Vector<Value> values = new Vector<Value>();
		String selection = "_id > " + date.getDate();
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, selection, null, null, null, "_id ASC", null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			values.add(new Value(new MyDate(cursor.getInt(0)), cursor.getFloat(1), cursor.getFloat(2)));
			cursor.moveToNext();
		}
		cursor.close();
		return values;
	}
	
	private void updateTrend(Value value) {
		ContentValues cv = new ContentValues();
		cv.put("trend", value.getTrend());
		
		SQLiteDatabase db = getWritableDatabase();
		db.update(TABLE_NAME, cv, "_id = " + value.getId(), null);
	}
	
	public void setSynched(Value value) {
		ContentValues cv = new ContentValues();
		cv.put("sync", true);
		
		SQLiteDatabase db = getWritableDatabase();
		db.update(TABLE_NAME, cv, "_id = " + value.getId(), null);
	}
	
	/**
	 * Calculates the trend for a arbitrary date. If the value exists in the database, that value is returned.<br>
	 * If not, a best-effort is made:<br>
	 * 1) if previous and next values exist, a new value is returned with the calculated trend. These may be identified by 
	 * the fact that the weight is set at 0.<br>
	 * 2) if only a previous or next value exists, then that value is returned. These may be identified by the fact that the
	 * value's date isn't the same as the requested date<br>
	 * 3) if the database is empty, a null value is returned. 
	 * @param date The requested date
	 * @return A Value for the given date, or the closest available. Returns null if the database is empty.
	 */
	public Value getTrendAtDate(MyDate date) {
		// Check if the database is empty. If it is, this exercise is meaningless and we can return 0
		if (isEmpty()) {
			return null;
		}
		
		// Check if there is a value for that date. If there is, simply return the trend.
		Value value = getValueByDate(date);
		if (value != null) {
			return value;
		}
		
		// Otherwise, get the values before and after the date
		Value prevValue = getValueBefore(date);
		Value nextValue = getValueAfter(date);
		
		// Check if they aren't null. If any is null, then we return the trend for the closest
		if (prevValue == null) {
			return nextValue;
		}
		else if (nextValue == null) {
			return prevValue;
		}
		
		// Otherwise, we do some trigonometry, baby!
		float t1 = prevValue.getTrend();
		float t2 = nextValue.getTrend();
		float dt = t2-t1;
		int dd = prevValue.getDate().differenceInDays(nextValue.getDate());
		int dx = prevValue.getDate().differenceInDays(date);
		float trend = t1 + (dx * dt / dd);
		return new Value(date, 0, trend);
	}
	
	public Value getGoalValue() {
		SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(context);
		float goalWeight = prefs.getFloat(Preferences.GOAL_WEIGHT, 0);
		
		if (goalWeight != cachedGoalWeight) {
			cachedGoalWeight = goalWeight;
			regenerateGoalValue();
		}
		return goalValue;
	}
	
	private void regenerateGoalValue() {
		if (getSize() < 2) {
			goalValue = null;
			return;
		}
		
		Value latestValue = getLatestValue();
		MyDate yesterday = new MyDate(latestValue.getDate());
		yesterday.addDays(-1);
		Value trendYesterday = getTrendAtDate(yesterday);
		float trendChange = latestValue.getTrend() - trendYesterday.getTrend();
		float changeToGoal = cachedGoalWeight - latestValue.getTrend();
		MyDate expectedDate;
		float goalTrend;
			if (cachedGoalWeight == 0 || trendChange * changeToGoal < 0) {
				// If the weight is trending away from the goal
				// we extrapolate where the trend will be by the end of the year
				expectedDate = new MyDate(latestValue.getDate().getYear(), 12, 31);
				int days = latestValue.getDate().differenceInDays(expectedDate);
				goalTrend = latestValue.getTrend() + trendChange * days;
			}
			else {
				int daysToGoal = (int) (changeToGoal / trendChange);
				expectedDate = new MyDate(latestValue.getDate());
				expectedDate.addDays(daysToGoal);
				goalTrend = cachedGoalWeight;
			}
		goalValue = new Value (expectedDate, 0, goalTrend);
	}
	
	public boolean isEmpty() {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null, "1");
		boolean isEmpty = false;
		if (cursor.getCount() == 0) {
			isEmpty = true;
		}
		cursor.close();
		return isEmpty;
	}
	
	/**
	 * Regenerates the trends for all the values after startValue.
	 * @param startValue The value from which to start calculating trends. If the value is null, the whole database is recalculated.
	 */
	public void regenerateTrendValues(Value startValue) {
		Vector<Value> values;
		if (startValue == null) {
			values = getAllValues(ORDER_ASC);
		}
		else {
			values = getValuesAfter(startValue.getDate());
		}
		int size = values.size();
		Value value;
		Value prevValue = startValue;
		float trend;
		for (int i = 0; i < size; i++) {
			value = values.elementAt(i);
			trend = Util.calculateTrend(prevValue, value);
			value.setTrend(trend);
			updateTrend(value);
			prevValue = value;
		}
		
		regenerateGoalValue();
	}
	
	public int getSize() {
		SQLiteDatabase db = getReadableDatabase();
		SQLiteStatement statement = db.compileStatement("SELECT COUNT(*) FROM " + TABLE_NAME);
		int result = (int) statement.simpleQueryForLong();
		statement.close();
		return result;
	}
	
	public int importFromFile(String path) throws IllegalArgumentException, FileNotFoundException {
		File file = new File(path);
		if (file == null) {
			throw new IllegalArgumentException("File should not be null.");
		}
		if (!file.exists()) {
			throw new FileNotFoundException("File does not exist: " + file);
		}
		if (!file.isFile()) {
			throw new IllegalArgumentException("Should not be a directory: " + file);
		}
		if (!file.canWrite()) {
			throw new IllegalArgumentException("File cannot be written: " + file);
		}
		
		Vector<Value> values = new Vector<Value>();
		try {
			BufferedReader input = new BufferedReader(new FileReader(file));
			try {
				String line;
				while ((line = input.readLine()) != null) {
					if (line.trim().equals("")) {
						continue;
					}
					try {
						values.add(new Value(line));
					} catch (IllegalArgumentException e) {
						throw e;
					}
				}
			} finally {
				input.close();
			}
		} catch (IOException ex) {
			ex.printStackTrace();
		}
		
		return insertManyValues(values);
	}
	
	public void exportToFile(String path) throws IOException {
		File file = new File(path);
		file.createNewFile();
		if (file == null) {
			throw new IllegalArgumentException("File should not be null.");
		}
		if (!file.isFile()) {
			throw new IllegalArgumentException("Should not be a directory: " + file);
		}
		if (!file.canWrite()) {
			throw new IllegalArgumentException("File cannot be written: " + file);
		}
		    
		Writer output = new BufferedWriter(new FileWriter(file));
		Vector<Value> values = getAllValues(ORDER_ASC);
		String line;
		for (Value value : values) {
			line = value.toFormatedString();
			output.write(line + "\n");
		}
		output.close();
	}
	
	private void sendUpdateIntent() {
		sendUpdateIntent(context);
	}
	
	public static void sendUpdateIntent(Context context) {
		Intent i = new Intent(DATABASE_CHANGED);
		context.sendBroadcast(i);
	}
}
